﻿using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CommTool
{
    public class NPOIExcel
    {
        private HSSFWorkbook _workBook;
        private ISheet _wbSheet = null;
        private DataColumnCollection _columns = null;
        private int _col = 0;   //total columns 
        private int _row = 0;   //total rows 
        private int _sheet = 0; //total sheets 
        private int _sheetRowNum = 65536; //each sheet allow rows 
        public NPOIExcel()
        {
            InstanceWorkBook();
        }
        /// <summary> 
        /// 实例方法 
        /// </summary> 
        /// <param name="sheetRowNum">单个表单允许的最大行数</param> 
        public NPOIExcel(int sheetRowNum)
        {
            _sheetRowNum = sheetRowNum;
            InstanceWorkBook();
        }

        /// <summary> 
        /// 实例方法 
        /// </summary> 
        /// <param name="columns">表头</param> 
        public NPOIExcel(DataColumnCollection columns)
        {
            _columns = columns;
            InstanceWorkBook();
        }

        #region CellStyles
        private ICellStyle _dateTimeCellStyle;
        private ICellStyle _doubleCellStyle;
        private ICellStyle _intCellStyle;


        private ICellStyle DoubleCellStyle
        {
            get
            {
                if (_doubleCellStyle != null) return _doubleCellStyle;

                _doubleCellStyle = _workBook.CreateCellStyle();
                _doubleCellStyle.DataFormat = 2;// _workBook.CreateDataFormat().GetFormat("#,##0");
                return _doubleCellStyle;
            }
        }

        private ICellStyle IntCellStyle
        {
            get
            {
                if (_intCellStyle != null) return _intCellStyle;

                _intCellStyle = _workBook.CreateCellStyle();
                _intCellStyle.DataFormat = 1;
                return _intCellStyle;
            }
        }

        private ICellStyle DateTimeCellStyle
        {
            get
            {
                if (_dateTimeCellStyle != null) return _dateTimeCellStyle;

                _dateTimeCellStyle = _workBook.CreateCellStyle();
                var dataFormat = _workBook.CreateDataFormat();

                _dateTimeCellStyle.DataFormat = dataFormat.GetFormat("yyyy-MM-dd HH:mm:ss");
                return _dateTimeCellStyle;
            }
        }

        #endregion

        private void InstanceWorkBook()
        {
            //cretate WorkBook 
            _workBook = new HSSFWorkbook();
            var dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "BaiyiTimes";
            _workBook.DocumentSummaryInformation = dsi;
            //create a entry of SummaryInformation 
            var si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "Etimes Secure Document System Log Backup";
            _workBook.SummaryInformation = si;
        }

        private DataColumnCollection GetColumns(DataColumnCollection columns)
        {
            return columns == null || columns.Count == 0 ? _columns : columns;
        }

        private ISheet GetSheet(ISheet sheet)
        {
            return sheet == null ? _wbSheet : sheet;
        }

        private void CreateHeader(ISheet sheet, DataColumnCollection columns)
        {
            _columns = GetColumns(columns);
            //create row of column 
            var oRow = sheet.CreateRow(0);
            foreach (DataColumn column in _columns)
            {
                var oCell = oRow.CreateCell(_col);
                //var style1 = _workBook.CreateCellStyle();
                //style1.FillForegroundColor = HSSFColor.LIGHT_GREEN.index;
                //style1.FillPattern = FillPatternType.SOLID_FOREGROUND;
                //style1.Alignment = HorizontalAlignment.CENTER;
                //style1.VerticalAlignment = VerticalAlignment.CENTER;
                //var font = _workBook.CreateFont();
                //font.Color = HSSFColor.BLACK.index;
                //style1.SetFont(font);
                //oCell.CellStyle = style1;
                var name = string.IsNullOrEmpty(column.Caption) ? column.ColumnName : column.Caption;
                oCell.SetCellValue(name);
                _col++;
            }
            // header belong to rows 
            _row++;
        }

        private void CreateHeader(ISheet sheet)
        {
            CreateHeader(sheet, null);
        }

        public ISheet CreateSheet()
        {
            return CreateSheet(null);
        }

        public ISheet CreateSheet(DataColumnCollection columns)
        {
            //旧页
            AutoSizeSheet(_wbSheet);

            _row = 0;
            _col = 0;
            _wbSheet = _workBook.CreateSheet(string.Format("Sheet{0}", _sheet + 1));
            CreateHeader(_wbSheet, columns);
            _sheet++;
            return _wbSheet;
        }

        public void SetRowValue(DataRowCollection rows, ISheet sheet)
        {
            _wbSheet = GetSheet(sheet);
            foreach (DataRow row in rows)
            {
                SetRowValue(row);
            }
            AutoSizeSheet(_wbSheet);
        }

        /// <summary>
        /// 自动调整页面列宽度
        /// </summary>
        /// <param name="sheet"></param>
        void AutoSizeSheet(ISheet sheet)
        {
            if (sheet == null) return;
            if (_columns == null) return;
            for (int i = 0; i < _columns.Count; i++)
            {
                sheet.AutoSizeColumn(i);
            }
        }

        public void SetRowValue(DataRowCollection rows)
        {
            SetRowValue(rows, null);
        }
        public void SetRowValue(DataRow row)
        {
            // create a new sheet 
            if (_row % _sheetRowNum == 0)
            {
                CreateSheet();
            }
            var oRow = _wbSheet.CreateRow(_row % _sheetRowNum);
            var obj = string.Empty;
            var cell = 0;


            foreach (DataColumn column in _columns)
            {
                if (column.DataType == typeof(double) || column.DataType == typeof(float))
                {
                    var val = Math.Round(Convert.ToDouble(row[column.ColumnName]), 3);
                    var newCell = oRow.CreateCell(cell, CellType.NUMERIC);
                    newCell.SetCellValue(val);
                    newCell.CellStyle = DoubleCellStyle;
                }
                else if (column.DataType == typeof(int) || column.DataType == typeof(uint)
                         || column.DataType == typeof(short) || column.DataType == typeof(ushort))
                {
                    var newCell = oRow.CreateCell(cell, CellType.NUMERIC);
                    newCell.SetCellValue(Convert.ToDouble(row[column.ColumnName]));
                    newCell.CellStyle = IntCellStyle;
                }
                else if (column.DataType == typeof(DateTime))
                {
                    var newCell = oRow.CreateCell(cell);
                    newCell.SetCellValue(Convert.ToDateTime(row[column.ColumnName]));
                    newCell.CellStyle = DateTimeCellStyle;
                }
                else
                {
                    obj = row[column.ColumnName].ToString();
                    oRow.CreateCell(cell).SetCellValue(obj);
                }

                cell++;
            }
            _row++;
        }

        public void SetProtectPassword(string password, string username)
        {
            _workBook.WriteProtectWorkbook(password, username);
        }

        public void SaveAs(string filePath)
        {
            for (int i = 0; i < _workBook.NumberOfSheets; i++)
            {
                var sheet = _workBook.GetSheetAt(i);
                AutoSizeSheet(sheet);
            }

            if (File.Exists(filePath)) File.Delete(filePath);
            var file = new FileStream(filePath, FileMode.Create);
            _workBook.Write(file);
            file.Close();
        }

    }
}